<?php
/**
 * php MysqlDB 读写分离类
 */

class Database {
	var $querynum = 0;
	//当前执行的sql语句
	var $cur_sql = null;
	//当前操作的数据库连接
	var $cur_link = null;
	var $linkr,$linkw,$charset,$pconnect,$dbconfig;

	function Database($dbarray,$dbcharset='utf8',$pcontent=0) {
		if(!is_array($dbarray)) {
			echo "数据库参数错误";
			return false;
		}
		$this->charset = $dbcharset;
		$this->pconnect = $pcontent;
		$this->dbconfig['master'] = $dbarray['master'];
		
		if(empty($dbarray['slave'])){
			$this->dbconfig['slave'] = false;
		}else{
			$dbSlaveServerNum = count($dbarray['slave']);
			if($dbSlaveServerNum >= 1) {
				//随机取出一个slave数据服务器
				$rand = rand(0,$dbSlaveServerNum-1);
				$this->dbconfig['slave'] = $dbarray['slave'][$rand];
			}else{
				$this->dbconfig['slave'] = false;
			}
		}
	}

	function connect($dbhost,$dbuser,$dbpw,$dbname) {
		if($this->pconnect) {
			$link = @mysql_pconnect($dbhost, $dbuser, $dbpw);
		} else {
			$link = @mysql_connect($dbhost, $dbuser, $dbpw, 1);
		}
		if($link) {
			if($this->version($link) > '4.1') {
				if($this->charset) {
					@mysql_query("SET character_set_connection=".$this->charset.", character_set_results=".$this->charset.", character_set_client=binary", $link);
				}

				if($this->version($link) > '5.0.1') {
					@mysql_query("SET sql_mode=''", $link);
				}
			}

			if($dbname) {
				@mysql_select_db($dbname, $link);
			}
			return $link;
		} else {
			error_log('mysql can not link'.mysql_error());
			return false;
		}
	}

	function connectM() {
		if(!$this->linkw = $this->connect($this->dbconfig['master']['host'],$this->dbconfig['master']['user'],$this->dbconfig['master']['pass'],$this->dbconfig['master']['database'])) {
			error_log("主数据库连接失败！");
		}
		//echo "<br>##connectM!.<br>";
	}

	function connectS() {
		if($this->dbconfig['slave']){
			if(!$this->linkr = $this->connect($this->dbconfig['slave']['host'],$this->dbconfig['slave']['user'],$this->dbconfig['slave']['pass'],$this->dbconfig['slave']['database'])) {
				$this->dbconfig['slave'] = false;
				$this->connectS();
			}
			//echo "<br>##connectS!.<br>";
		} else {
			if(!$this->linkw) {
				$this->connectM();
			}
			$this->linkr = $this->linkw;
		}
	}

	// db read
	function query($sql, $type = '') {
		if(!$this->linkr) {
			$this->connectS();
		}

		//设置当前数据库连接
		$this->cur_link = $this->linkr;
		$this->cur_sql = $sql;
		//判断是否select语句
		if(!preg_match ("/^(\s*)select/i", $sql)){
			exit('sql must select!');
			return false;
		}

		$func = $type == 'UNBUFFERED' && @function_exists('mysql_unbuffered_query') ? 'mysql_unbuffered_query' : 'mysql_query';
		$query = $func($sql, $this->linkr);

		$this->querynum++;
		
		if(!$query){
			$this->debug();
		}
		
		return $query;
	}
	// db write
	function execute($sql, $type = '') {
		if(!$this->linkw) {
			$this->connectM();
		}

		//设置当前数据库连接
		$this->cur_link = $this->linkw;
		$this->cur_sql = $sql;

		$func = $type == 'UNBUFFERED' && @function_exists('mysql_unbuffered_query') ? 'mysql_unbuffered_query' : 'mysql_query';
		$query = $func($sql, $this->linkw);

		$this->querynum++;
		
		if(!$query){
			$this->debug();
		}
		
		return $query;
	}


	//记录sql查询时的错误日志
	function debug($msg = ''){
		if($msg!=''){
			error_log($msg);
		}else{
			error_log($this->error()."\t".$this->cur_sql);
		}
		return FALSE;
	}

	//获得sql语句中的字符串
	function getString($sql){
		$result = $this->query($sql);
		if($result){
			$numRows = mysql_num_rows($result);
			if($numRows==1){
				$arr = mysql_fetch_array($result);
				if($arr){
					return $arr[0];
				}
			}else if($numRows>1){
				return $this->debug("ERROR getString: results more than ONE row: $numRows, sql: $sql");
			}else if($numRows==0){
				return FALSE;
			}
		}
		return FALSE;
	}
	//查询记录条数,例如select count(*) from table;
	function getNumber($sql){
		$ret = $this->getString($sql);
		if($ret!==FALSE&&$ret!==null){
			if(is_numeric($ret)){
				return $ret;
			}else{
				$this->debug("ERROR getNumber: not a number: $ret, sql: $sql");
				return 0;
			}
		}
		return 0;
	}
	//获取一条记录,例如select id from table limit 1;
	function getRecord($sql){
		$result = $this->query($sql);
		if($result){
			$numRows = mysql_num_rows($result);
			if($numRows==1)
			return mysql_fetch_assoc($result);
			else if($numRows>1)
			return $this->debug("ERROR getRecord: results more than ONE row: $numRows, sql: $sql");
			else if($numRows==0)
			return FALSE;
		}
		return FALSE;
	}

	//查询多条记录
	function getRecords($sql){
		$result = $this->query ($sql);
		if ($result && mysql_num_rows ($result) > 0) {
			$all = array ();
			while ( $arr = mysql_fetch_assoc ( $result ) )
			$all [] = $arr;
			return $all;
		}else{
			return array();
		}
	}

	function getRecordsByPage($page,$pageSize,$sql){
		if(!$pageSize){
			return $this->debug("ERROR getRecordsByPage: pageSize = 0");
		}

		if(preg_match("/GROUP\sBY\s.*\sHAVING\s/is",$sql,$arr)){
			$result = $this->query($sql);
			$total = mysql_num_rows($result);
			mysql_free_result($result);
		}else{
			if(preg_match("/group\sby\s?([^\s$]+)/is",$sql,$arr)){
				$countsql = "SELECT COUNT(DISTINCT({$arr[1]})) ";
			}else{
				$countsql = "SELECT COUNT(*) ";
			}
			if(preg_match("/[)\s]+(from\s.*)/is",$sql,$arr)){
				$countsql .= preg_replace("/(group\sby\s.*)/is","",$arr[1]);
			}else{
				$this->debug("ERROR: getRecordsByPage: can not find 'FROM' clause");
				return FALSE;
			}
			$total = $this->getNumber($countsql);
		}
		if($total==0)
		return FALSE;

		$pageCount = ceil($total/$pageSize);
		$page = ($page>$pageCount)?$pageCount:(($page>0)?$page:1);

		$from = ($page-1)*$pageSize;
		$sql .= " LIMIT $from,$pageSize";

		$data = $this->getRecords($sql);
		if(!$data)
		return FALSE;

		$arr = array("page"=>$page, "pageSize"=>$pageSize, "pageCount"=>$pageCount, "total"=>$total, "data"=>$data);
		return $arr;
	}

	function fetch_array($query, $result_type = MYSQL_ASSOC) {
		return @mysql_fetch_array($query, $result_type);
	}

	function fetch_row($query) {
		$query = mysql_fetch_row($query);
		return $query;
	}

	function fetch_One($sql) {
		$query = $this->query($sql);
		return $this->fetch_row($query);
	}
	//和updateRecord方法一样
	function update($table,$where,$array) {
		$whereset = $set = $dot = '';
		foreach($array as $key=>$value) {
			$set .= $dot." `$key`='$value'";
			$dot = ',';
		}
		$whereset = $dot = '';
		foreach($where as $k=>$v) {
			$whereset .= $dot." `$k`='$v'";
			$dot = ' and ';
		}

		$sql = "update $table set $set where $whereset";
		return $this->execute($sql);
	}
	//和addRecord方法一样
	function insert($table,$array) {
		$col = $v = $dot = '';
		foreach($array as $key=>$value) {
			$v .= $dot."'$value'";
			$col .= $dot."`$key`";
			$dot = ',';
		}
		$sql = "insert into $table ($col) values ($v)";
		if($this->execute($sql)) {
			return $this->insert_id();
		} else {
			return false;
		}
	}
	//批量更新记录
	function updateRecord($table,$cond,$data){
		if(empty($table)||empty($cond)||!is_array($data)||count($data)==0){
			return $this->debug("ERROR updateRecord: table: $table, cond: $cond, data: $data");
		}

		$str = "";
		foreach($data as $key=>$val){
			$str .= ", ".$this->parseKey($key)."=" . $this->parseValue($val);
		}

		if($str!=""){
			$str[0] = ' ';
			$sql = "UPDATE $table SET $str WHERE $cond";
			return $this->execute($sql);
		}
		return FALSE;
	}
	//批量添加记录
	function addRecord($table,$data){
		if(empty($table)||!is_array($data)||count($data)==0){
			return $this->debug("ERROR addRecord: table: $table, data: $data");
		}

		$items = "";
		$values = "";
		foreach($data as $key=>$val){
			$items .= ", $key";
			$values .= ", '".$this->escape_string($val)."'";
		}
		if($items!=""&&$values!=""){
			$items[0] = ' ';
			$values[0] = ' ';
			$sql = "INSERT INTO $table ($items) VALUES ($values)";
			$ret = $this->execute($sql);
			if($ret){
				return $this->insert_id();
			}
		}
		return FALSE;
	}


	/**
	 * value分析
	 * @access protected
	 * @param mixed $value
	 * @return string
	 */
	protected function parseValue($value) {
		if(is_string($value)) {
			$value = '\''.$this->escape_string($value).'\'';
		}elseif(isset($value[0]) && is_string($value[0]) && strtolower($value[0]) == 'exp'){
			$value   =  $this->escape_string($value[1]);
		}elseif(is_null($value)){
			$value   =  'null';
		}
		return $value;
	}

	/**
	 * 字段和表名处理添加
	 * @access protected
	 * @param string $key
	 * @return string
	 */
	protected function parseKey(&$key) {
		$key   =  trim($key);
		if(!preg_match('/[,\'\"\*\(\)`.\s]/',$key)) {
			$key = '`'.$key.'`';
		}
		return $key;
	}

	public function escape_string($str) {
		return mysql_real_escape_string($str);
	}


	function insert_id() {
		return ($id = mysql_insert_id($this->linkw)) >= 0 ? $id : $this->result($this->execute("SELECT last_insert_id()"), 0);
	}

	// db other
	function affected_rows() {
		return mysql_affected_rows($this->cur_link);
	}

	function error() {
		return 'Mysql Error:'.(($this->cur_link) ? mysql_error($this->cur_link) : mysql_error());
	}

	function errno() {
		return 'Mysql Errno:'.intval(($this->cur_link) ? mysql_errno($this->curlink) : mysql_errno());
	}

	function result($query, $row) {
		$query = @mysql_result($query, $row);
		return $query;
	}

	function num_rows($query) {
		$query = mysql_num_rows($query);
		return $query;
	}

	function num_fields($query) {
		return mysql_num_fields($query);
	}

	function free_result($query) {
		return mysql_free_result($query);
	}

	function fetch_fields($query) {
		return mysql_fetch_field($query);
	}

	function version($link) {
		return mysql_get_server_info($link);
	}

	function close() {
		if($this->cur_link){
			mysql_close($this->cur_link);
		}
		return true;
	}
	function __destruct(){
		$this->close();
	}
}

/* 测试例子 //
 * $db_config['master'] = array("host"=>"10.4.0.52","database"=>"1mobile_user","user"=>"root","pass"=>"");
 * $db_config['slave'] = array(array("host"=>"10.4.0.52","database"=>"1mobile_user","user"=>"root","pass"=>""),
 array("host"=>"10.4.0.52","database"=>"1mobile_user","user"=>"root","pass"=>""));
 * $newdb = new Database($db_config,'utf8');
 * $array = array('name' => date("Y-m-d H:i:s"));
 * $id = $newdb->insert("test",$array);
 * $result = $newdb->fetch_page("select * from test order by id desc",'10');
 * print_r($result);
 * $id = $newdb->insert("test",$array);
 * echo $id;
 */